﻿'The budgeting system is for calculating the budget vs actual (Premium, Commission)
'The actual is calculated from the Policies table
'The budget is saved/caculated from the UnderwritingBudget
'----------
'When a report is generated it calculates the budget for the selected
'period by dividing the annual period by no. of days in the selected period
'----------
'Pending:
'1) Calculate budget vs. no. of days in period

Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmTargetCompany

    Sub FillBudget()
        Try
            If Me.CombBranch.SelectedIndex = -1 OrElse Me.CombBranch.SelectedIndex = -1 Then
                Exit Sub
            End If

            Me.Cursor = Cursors.WaitCursor

            Me.GridBudget.Rows.Clear()

            Dim cmd As New SqlCommand("Select Distinct ClassName," & _
                                      "IsNull(dbo.GetPremiumBudget(ClassName,N'" & Me.CombYear.SelectedItem & _
                                      "',N'" & Me.CombBranch.SelectedItem & "'),0) Commission," & _
                                      "IsNull(dbo.GetCommissionBudget(ClassName,N'" & Me.CombYear.SelectedItem & _
                                      "',N'" & Me.CombBranch.SelectedItem & "'),0) Premium " & _
                                      "From InsurClasses", cnn)
            Dim Reader As SqlDataReader

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridBudget.Rows.Add(New String() {Reader.Item("ClassName"), CDbl(Reader.Item("Commission")).ToString("N2"), _
                                                     CDbl(Reader.Item("Premium")).ToString("N2")})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub CombYear_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombYear.SelectedIndexChanged
        Me.GridBudget.Rows.Clear()
        FillBudget()
    End Sub

    Private Sub CombJeddah_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombBranch.SelectedIndexChanged
        Me.GridBudget.Rows.Clear()
        FillBudget()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Me.CombYear.SelectedIndex = -1
        Me.CombBranch.SelectedIndex = -1
        Me.GridBudget.Rows.Clear()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If Me.CombYear.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombYear, "Please select a valid year from the list")

        ElseIf Me.CombBranch.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombBranch, "Please select a valid year from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cmd.Connection = cnn

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Delete old data
                cmd.CommandText = "Delete From UnderwritingBudget Where Year=N'" & Me.CombYear.SelectedItem & _
                                  "' and Branch=N'" & Me.CombBranch.SelectedItem & "'"
                cmd.ExecuteNonQuery()

                'Save new year budget
                For Each row As DataGridViewRow In Me.GridBudget.Rows
                    cmd.CommandText = "Insert Into UnderwritingBudget (Year,Branch,Class,Premium,Commission) " & _
                                      "Values (@Year,@Branch,@Class,@Premium,@Commission)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@Year", Me.CombYear.SelectedItem)
                    cmd.Parameters.AddWithValue("@Branch", Me.CombBranch.SelectedItem)
                    cmd.Parameters.AddWithValue("@Class", row.Cells(0).Value)
                    cmd.Parameters.AddWithValue("@Premium", CDbl(row.Cells(1).Value))
                    cmd.Parameters.AddWithValue("@Commission", CDbl(row.Cells(2).Value))
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridBudget_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridBudget.CellEndEdit
        Try
            Me.GridBudget.CurrentCell.Value = CDbl(Me.GridBudget.CurrentCell.Value).ToString("N2")
        Catch ex As Exception
            Me.GridBudget.CurrentCell.Value = "0.00"
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.ErrProv.Clear()
        Try
            If Me.RBranch.Checked = True And Me.CombBranchRpt.SelectedIndex = -1 Then
                Me.ErrProv.SetError(Me.CombBranchRpt, "Please select a valid branch from the list")
            Else
                Me.Cursor = Cursors.WaitCursor

                Dim CommBudget, CommAcutal, PremBudget, PremAcutal As Double
                Dim MinMonth, MaxMonth, Branch As String
                Dim das As New DsUnderwritingBudget
                Dim InsurClasses As ArrayList = GetInsurClassList()
                Dim Year As Integer = Me.DTPPeriodFRm.Value.Year


                '1) Set parameter
                MinMonth = Me.DTPPeriodFRm.Value.Month
                MaxMonth = Me.DTPPeriodTo.Value.Month
                Branch = Me.CombBranchRpt.SelectedItem

                '2) Fill data
                For Each InsurClass As String In InsurClasses
                    If Me.RBranch.Checked = True Then
                        CommBudget = GetCommBudget(InsurClass, Year, Branch)
                        CommAcutal = GetCommActual(InsurClass, Year, MinMonth, MaxMonth, Branch)
                        PremBudget = GetPremBudget(InsurClass, Year, Branch)
                        PremAcutal = GetPremActual(InsurClass, Year, MinMonth, MaxMonth, Branch)

                    ElseIf Me.RAll.Checked = True Then
                        CommBudget = GetCommBudget(InsurClass, Year, "Head Office") + GetCommBudget(InsurClass, Year, "Jeddah") +
                                     GetCommBudget(InsurClass, Year, "Riyadh") + GetCommBudget(InsurClass, Year, "Alkhober")

                        CommAcutal = GetCommActual(InsurClass, Year, MinMonth, MaxMonth, "Head Office") + GetCommActual(InsurClass, Year, MinMonth, MaxMonth, "Jeddah") +
                                     GetCommActual(InsurClass, Year, MinMonth, MaxMonth, "Riyadh") + GetCommActual(InsurClass, Year, MinMonth, MaxMonth, "Alkhober")

                        PremBudget = GetPremBudget(InsurClass, Year, "Head Office") + GetPremBudget(InsurClass, Year, "Jeddah") +
                                     GetPremBudget(InsurClass, Year, "Riyadh") + GetPremBudget(InsurClass, Year, "Alkhober")

                        PremAcutal = GetPremActual(InsurClass, Year, MinMonth, MaxMonth, "Head Office") + GetPremActual(InsurClass, Year, MinMonth, MaxMonth, "Jeddah") +
                                     GetPremActual(InsurClass, Year, MinMonth, MaxMonth, "Riyadh") + GetPremActual(InsurClass, Year, MinMonth, MaxMonth, "Alkhober")

                    End If
                    das.Tables(0).Rows.Add(New String() {InsurClass, PremBudget, PremAcutal, CommBudget, CommAcutal})
                Next

                If Me.RNumbers.Checked = True Then
                    ShowBudgetNumbers(das)

                ElseIf Me.RChart.Checked = True Then
                    ShowBudgetChart(das)
                End If

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowBudgetNumbers(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New UnderwritingBudget
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranchRpt.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub ShowBudgetChart(ByVal das As DataSet)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New UnderwritingBudgetChart
            rpt.SetDataSource(das)

            'Cr Parameter

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Function GetCommBudget(ByVal ClassName As String, ByVal Year As Integer, ByVal Branch As String) As Double
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim CommBudget As Double
            Dim cmd As New SqlCommand("Select IsNull(dbo.GetCommissionBudget(N'" & ClassName & "',N'" & Year & "',N'" & Branch & "'),0)", cnn)

            cnn.Open()
            CommBudget = CDbl(cmd.ExecuteScalar)
            cnn.Close()

            'Calcualte amount vs. no. of months selected in period
            Dim Months As Integer = DateDiff(DateInterval.Month, Me.DTPPeriodFRm.Value, Me.DTPPeriodTo.Value) + 1
            CommBudget = Months * (CommBudget / 12)

            Me.Cursor = Cursors.Default

            Return CommBudget
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Function GetCommActual(ByVal ClassName As String, ByVal Year As Integer, ByVal MinMonth As Integer, ByVal MaxMonth As String, ByVal Branch As String) As Double
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim CommActual As Double
            Dim cmd As New SqlCommand("Select IsNull(dbo.GetCommissionActual(N'" & ClassName & "'," & Year & "," & MinMonth & _
                                      "," & MaxMonth & ",N'" & Branch & "'),0)", cnn)

            cnn.Open()
            CommActual = CDbl(cmd.ExecuteScalar)
            cnn.Close()

            Me.Cursor = Cursors.Default

            Return CommActual
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Function GetPremBudget(ByVal ClassName As String, ByVal Year As Integer, ByVal Branch As String) As Double
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim PremBudget As Double
            Dim cmd As New SqlCommand("Select IsNull(dbo.GetPremiumBudget(N'" & ClassName & "',N'" & Year & "',N'" & Branch & "'),0)", cnn)

            cnn.Open()
            PremBudget = CDbl(cmd.ExecuteScalar)
            cnn.Close()

            'Calcualte amount vs. no. of months selected in period
            Dim Months As Integer = DateDiff(DateInterval.Month, Me.DTPPeriodFRm.Value, Me.DTPPeriodTo.Value) + 1
            PremBudget = Months * (PremBudget / 12)

            Me.Cursor = Cursors.Default

            Return PremBudget
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Function GetPremActual(ByVal ClassName As String, ByVal Year As Integer, ByVal MinDate As String, ByVal MaxDate As String, ByVal Branch As String) As Double
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Premctual As Double
            Dim cmd As New SqlCommand("Select IsNull(dbo.GetPremiumActual(N'" & ClassName & "'," & Year & "," & MinDate & _
                                      "," & MaxDate & ",N'" & Branch & "'),0)", cnn)

            cnn.Open()
            Premctual = CDbl(cmd.ExecuteScalar)
            cnn.Close()

            Me.Cursor = Cursors.Default

            Return Premctual
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Private Sub DTPPeriodFRm_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPPeriodFRm.ValueChanged
        Me.DTPPeriodTo.Value = New Date(Me.DTPPeriodFRm.Value.Year, Me.DTPPeriodTo.Value.Month, Me.DTPPeriodTo.Value.Day)
    End Sub

    Private Sub DTPPeriodTo_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPPeriodTo.ValueChanged
        Me.DTPPeriodFRm.Value = New Date(Me.DTPPeriodTo.Value.Year, Me.DTPPeriodFRm.Value.Month, Me.DTPPeriodFRm.Value.Day)
    End Sub

    Private Sub RBranch_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RBranch.CheckedChanged
        If Me.RBranch.Checked = True Then
            Me.CombBranchRpt.Enabled = True
        ElseIf Me.RAll.Checked = True Then
            Me.CombBranchRpt.Enabled = False
        End If
    End Sub

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombBranch.Items.Clear()
            Me.CombBranchRpt.Items.Clear()

            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
                Me.CombBranchRpt.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmUnderwritingBudget_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
    End Sub
End Class